




ALTER    function fn_wip_process_view(@product_id int)
returns @fn_results Table(ProcessStep_ID bigint,
			  PS_Type varchar(20),
			  Location_ID int,
			  Location varchar(80),
			  Lot_ID bigint,
			  Lot_Number varchar(20)
			  )
AS
begin
	declare @results Table(ProcessStep_ID bigint,
			  	PS_Type varchar(20),
			  	Location_ID int,
			  	Location varchar(80),
			  	Lot_ID bigint,
			  	Lot_Number varchar(20)
			  	)
	declare @flow_cursor CURSOR
	declare @location_cursor CURSOR
	declare @lot_cursor CURSOR
	declare @location_name varchar(80)
	declare @temp_ps_type varchar(20)
	declare @temp_ps_type_name varchar(20)
	declare @temp_process_step_id bigint
	declare @temp_location_id int
	declare @temp_lot_number varchar(20)
	declare @temp_lot_id bigint
	declare @temp_count int
	declare @temp_flag int
	set @temp_flag = 0

	set @temp_process_step_id = (select ProcessStep_ID from Product where Product_ID = @product_id)
	set @flow_cursor = CURSOR FOR
	select *from fn_get_process_flow_backward_by_id(@temp_process_step_id) order by Temp_Count DESC

	open @flow_cursor
		fetch next from @flow_cursor into @temp_count, @temp_process_step_id, @temp_ps_type, @temp_ps_type_name

		while @@fetch_status = 0
		begin

			set @location_cursor = CURSOR FOR
			select Location_ID from Process_Step_Location_Table where ProcessStep_ID = @temp_process_step_id

			open @location_cursor

				fetch next from @location_cursor into @temp_location_id

				while @@fetch_status = 0
				begin
					set @location_name = (select l.L_Vendor_Name+'/'+a.City from Location l, Address a where l.Location_ID =@temp_location_id and l.Address_ID = a.Address_ID)
					set @lot_cursor = CURSOR FOR
					select Lot_ID, Lot_Number from Lot_Table where ProcessStep_ID = @temp_process_step_id and Location_ID = @temp_location_id and Lot_Current_Status = 'Running'

					open @lot_cursor
						fetch next from @lot_cursor into @temp_lot_id, @temp_lot_number
						while @@fetch_status = 0
						begin
							if @temp_ps_type_name = 'FAB'
							begin
								insert into @results(ProcessStep_ID, PS_Type , Location_ID, Location, Lot_ID, Lot_Number)
								values(@temp_process_step_id, @temp_ps_type_name, @temp_location_id, @location_name, @temp_lot_id, @temp_lot_number)
								set @temp_flag = 1
							end
							else
							begin
								insert into @results(ProcessStep_ID, PS_Type , Location_ID, Location, Lot_ID, Lot_Number)
								values(@temp_process_step_id, @temp_ps_type, @temp_location_id, @location_name, @temp_lot_id, @temp_lot_number)
								set @temp_flag = 1
							end
							fetch next from @lot_cursor into @temp_lot_id, @temp_lot_number
						end

					close @lot_cursor
					deallocate @lot_cursor
					if @temp_flag = 1
					begin
						set @temp_flag = 0
					end
					else
					begin
						insert into @results(ProcessStep_ID, PS_Type , Location_ID, Location)
						values(@temp_process_step_id, @temp_ps_type, @temp_location_id, @location_name)						
					end

					fetch next from @location_cursor into @temp_location_id
				end

			close @location_cursor
			deallocate @location_cursor

			fetch next from @flow_cursor into @temp_count, @temp_process_step_id, @temp_ps_type, @temp_ps_type_name
		end
	close @flow_cursor
	deallocate @flow_cursor

	insert @fn_results select *from @results
	return
end



